/**
 * Migration: Create Initial Tables
 * Created: 2024-12-17T12:00:00.000Z
 */

module.exports = {
  async up(connection, dbType) {
    // 创建用户表
    const createUsersTable = dbType === 'sqlite' 
      ? `CREATE TABLE IF NOT EXISTS users (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          username TEXT UNIQUE NOT NULL,
          email TEXT UNIQUE NOT NULL,
          password_hash TEXT NOT NULL,
          full_name TEXT,
          phone TEXT,
          avatar_url TEXT,
          status TEXT DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'suspended')),
          email_verified INTEGER DEFAULT 0,
          phone_verified INTEGER DEFAULT 0,
          last_login_at DATETIME,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )`
      : `CREATE TABLE IF NOT EXISTS users (
          id INT PRIMARY KEY AUTO_INCREMENT,
          username VARCHAR(50) UNIQUE NOT NULL,
          email VARCHAR(100) UNIQUE NOT NULL,
          password_hash VARCHAR(255) NOT NULL,
          full_name VARCHAR(100),
          phone VARCHAR(20),
          avatar_url TEXT,
          status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
          email_verified BOOLEAN DEFAULT FALSE,
          phone_verified BOOLEAN DEFAULT FALSE,
          last_login_at TIMESTAMP NULL,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          INDEX idx_username (username),
          INDEX idx_email (email),
          INDEX idx_status (status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`;

    // 创建管理员表
    const createAdminsTable = dbType === 'sqlite'
      ? `CREATE TABLE IF NOT EXISTS admins (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          username TEXT UNIQUE NOT NULL,
          email TEXT UNIQUE NOT NULL,
          password_hash TEXT NOT NULL,
          full_name TEXT,
          role TEXT DEFAULT 'admin' CHECK (role IN ('super_admin', 'admin', 'moderator')),
          permissions TEXT,
          status TEXT DEFAULT 'active' CHECK (status IN ('active', 'inactive')),
          last_login_at DATETIME,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )`
      : `CREATE TABLE IF NOT EXISTS admins (
          id INT PRIMARY KEY AUTO_INCREMENT,
          username VARCHAR(50) UNIQUE NOT NULL,
          email VARCHAR(100) UNIQUE NOT NULL,
          password_hash VARCHAR(255) NOT NULL,
          full_name VARCHAR(100),
          role ENUM('super_admin', 'admin', 'moderator') DEFAULT 'admin',
          permissions JSON,
          status ENUM('active', 'inactive') DEFAULT 'active',
          last_login_at TIMESTAMP NULL,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          INDEX idx_username (username),
          INDEX idx_email (email),
          INDEX idx_role (role)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`;

    // 创建菜单分类表
    const createMenuCategoriesTable = dbType === 'sqlite'
      ? `CREATE TABLE IF NOT EXISTS menu_categories (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          name TEXT NOT NULL,
          description TEXT,
          sort_order INTEGER DEFAULT 0,
          status TEXT DEFAULT 'active' CHECK (status IN ('active', 'inactive')),
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )`
      : `CREATE TABLE IF NOT EXISTS menu_categories (
          id INT PRIMARY KEY AUTO_INCREMENT,
          name VARCHAR(100) NOT NULL,
          description TEXT,
          sort_order INT DEFAULT 0,
          status ENUM('active', 'inactive') DEFAULT 'active',
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          INDEX idx_sort_order (sort_order),
          INDEX idx_status (status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`;

    // 创建菜单项表
    const createMenuItemsTable = dbType === 'sqlite'
      ? `CREATE TABLE IF NOT EXISTS menu_items (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          category_id INTEGER,
          name TEXT NOT NULL,
          description TEXT,
          price DECIMAL(10,2) NOT NULL,
          image_url TEXT,
          ingredients TEXT,
          allergens TEXT,
          nutritional_info TEXT,
          availability INTEGER DEFAULT 1,
          sort_order INTEGER DEFAULT 0,
          status TEXT DEFAULT 'active' CHECK (status IN ('active', 'inactive')),
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
          FOREIGN KEY (category_id) REFERENCES menu_categories(id) ON DELETE SET NULL
        )`
      : `CREATE TABLE IF NOT EXISTS menu_items (
          id INT PRIMARY KEY AUTO_INCREMENT,
          category_id INT,
          name VARCHAR(200) NOT NULL,
          description TEXT,
          price DECIMAL(10,2) NOT NULL,
          image_url TEXT,
          ingredients TEXT,
          allergens TEXT,
          nutritional_info JSON,
          availability BOOLEAN DEFAULT TRUE,
          sort_order INT DEFAULT 0,
          status ENUM('active', 'inactive') DEFAULT 'active',
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          INDEX idx_category_id (category_id),
          INDEX idx_price (price),
          INDEX idx_availability (availability),
          INDEX idx_sort_order (sort_order),
          FOREIGN KEY (category_id) REFERENCES menu_categories(id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`;

    // 执行创建表的SQL
    if (dbType === 'sqlite') {
      await connection.run(createUsersTable);
      await connection.run(createAdminsTable);
      await connection.run(createMenuCategoriesTable);
      await connection.run(createMenuItemsTable);
    } else {
      await connection.execute(createUsersTable);
      await connection.execute(createAdminsTable);
      await connection.execute(createMenuCategoriesTable);
      await connection.execute(createMenuItemsTable);
    }

    console.log('✅ Created initial tables: users, admins, menu_categories, menu_items');
  },

  async down(connection, dbType) {
    // 删除表（注意顺序，先删除有外键依赖的表）
    const tables = ['menu_items', 'menu_categories', 'admins', 'users'];
    
    for (const table of tables) {
      const dropTableSQL = `DROP TABLE IF EXISTS ${table}`;
      
      if (dbType === 'sqlite') {
        await connection.run(dropTableSQL);
      } else {
        await connection.execute(dropTableSQL);
      }
    }

    console.log('✅ Dropped initial tables: users, admins, menu_categories, menu_items');
  }
};